package com.geeaks.generator.util;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import com.alibaba.fastjson.JSON;
import com.geeaks.generator.enums.EnumDbType;
import com.geeaks.generator.model.DatabaseConfigBo;
import com.geeaks.generator.model.RowInfoBo;
import com.geeaks.generator.model.TableInfoBo;
import com.geeaks.generator.model.UITableColumnBo;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;

/**
 * @Description: DB工具类
 * @author gaoxiang
 * @date 2019-04-15 11:37:20
 */
public class DbUtil {
	
	private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;
	
	public static void main(String[] args) throws Exception {
		List<DatabaseConfigBo> loadDatabaseConfig = ConfigHelper.loadDatabaseConfig();
		TableInfoBo tableInfo = getTableInfo(loadDatabaseConfig.get(0), "invoice_record");
		System.out.println(JSON.toJSONString(tableInfo));
	}
	
	public static Connection getConnection(DatabaseConfigBo config) throws ClassNotFoundException, SQLException {
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		EnumDbType dbType = EnumDbType.valueOf(config.getDbType());
		System.err.println(dbType.getDriverClass());
		Class.forName(dbType.getDriverClass());
		String url = getConnectionUrlWithSchema(config);
		return DriverManager.getConnection(url, config.getUsername(), config.getPassword());
	}
	
	public static List<String> getTableNames(DatabaseConfigBo config) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();
		// ResultSet rs = md.getTables(null, config.getUsername().toUpperCase(),
		// null, null);
		ResultSet rs = md.getTables(null, "%", "%", new String[] { "TABLE" });
		List<String> tables = new ArrayList<>();
		while (rs.next()) {
			tables.add(rs.getString(3));
		}
		return tables;
	}
	
	public static List<UITableColumnBo> getTableColumns(DatabaseConfigBo dbConfig, String tableName) throws Exception {
		Connection conn = getConnection(dbConfig);
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = md.getColumns(null, null, tableName, null);
		List<UITableColumnBo> columns = new ArrayList<>();
		while (rs.next()) {
			UITableColumnBo columnVO = new UITableColumnBo();
			String columnName = rs.getString("COLUMN_NAME");
			columnVO.setColumnName(columnName);
			columnVO.setJdbcType(rs.getString("TYPE_NAME"));
			columns.add(columnVO);
		}
		return columns;
	}
	
	public static String getConnectionUrlWithSchema(DatabaseConfigBo dbConfig) throws ClassNotFoundException {
		EnumDbType dbType = EnumDbType.valueOf(dbConfig.getDbType());
		String connectionUrl = String.format(dbType.getConnectionUrlPattern(), dbConfig.getHost(), dbConfig.getPort(), 
				dbConfig.getSchema(), dbConfig.getEncoding());
//		connectionUrl = "jdbc:mysql://yongjiatest.db.58dns.org:53639/db58_zqchunyun_test?serverTimezone=Asia/Shanghai&connectTimeout=1000&useUnicode=true&autoReconnect=true&useSSL=false";
		return connectionUrl;
	}
	
	public static String getTableDesc(DatabaseConfigBo dbConfig, String tableNameField) throws Exception {
		Connection conn = getConnection(dbConfig);
		String sql = "show create table " + tableNameField;
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet resultSet = pstmt.executeQuery(sql);
		while (resultSet.next()) {
			String jbsql = resultSet.getString(2);
			String comment = getComment(jbsql);
			return comment;
		}
		return tableNameField;
	}
	
	public static TableInfoBo getTableInfo(DatabaseConfigBo dbConfig, String tableNameField) throws Exception {
		Connection conn = getConnection(dbConfig);
		String sql = "show create table " + tableNameField;
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet resultSet = pstmt.executeQuery(sql);
		TableInfoBo tableInfoBo = new TableInfoBo();
		tableInfoBo.setTableName(tableNameField);
		while (resultSet.next()) {
			//建表SQL
			String jbsql = resultSet.getString(2);
			tableInfoBo.setTableDesc(getComment(jbsql));
			List<String> list = Splitter.on("\n").splitToList(jbsql);
			List<RowInfoBo> tableColumns = Lists.newArrayList();
			for(String line : list) {
				RowInfoBo rowInfoBo = new RowInfoBo();
				line = line.trim();
				rowInfoBo.setNullAble(!StringUtils.containsIgnoreCase(line, "NOT NULL"));
				line = StringUtils.remove(line, "NOT NULL");
				line = StringUtils.removeEnd(line, ",");
				line = StringUtils.remove(line, "AUTO_INCREMENT");
				line = StringUtils.remove(line, "unsigned");
				line = StringUtils.remove(line, "COLLATE");
				line = StringUtils.remove(line, "utf8_bin");
				if(StringUtils.startsWith(line, "`")) {
					List<String> columns = Splitter.on(" ").omitEmptyStrings().trimResults().splitToList(line);
					rowInfoBo.setField(StringUtils.remove(columns.get(0), "`"));
					String type = columns.get(1);
					if(StringUtils.contains(type, "(")) {
						rowInfoBo.setType(StringUtils.substring(type, 0, type.indexOf('(')));
					} else {
						rowInfoBo.setType(StringUtils.substring(type, 0));
					}
					if(StringUtils.contains(type, "(")) {
						String lenthDesc = StringUtils.substring(type, type.indexOf('(')+1, type.indexOf(')'));
						if(StringUtils.contains(lenthDesc, ",")) {
							String lengthStr = StringUtils.substring(lenthDesc, 0, lenthDesc.indexOf(','));
							rowInfoBo.setLength(Integer.valueOf(lengthStr));
							String delimiterLength = StringUtils.substring(lenthDesc, lenthDesc.indexOf(',')+1);
							rowInfoBo.setDelimiterLength(Integer.valueOf(delimiterLength));
						} else {
							rowInfoBo.setLength(Integer.valueOf(lenthDesc));
						}
					}
					if(StringUtils.containsIgnoreCase(line, "COMMENT")) {
						String comment = "";
						int i = -1;
						for(int x=0;x<columns.size();x++) {
							if(columns.get(x).equals("COMMENT")) {
								i = x+1;
							}
							if(i!= -1 && x>=i) {
								comment += columns.get(x);
							}
						}
						rowInfoBo.setComment(StringUtils.replace(comment, "'", ""));
					}
					tableColumns.add(rowInfoBo);
				}
			}
			tableInfoBo.setTableColumns(tableColumns);
		}
		return tableInfoBo;
	}
	
	public static String getComment(String all) {
		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return "";
		}
		comment = all.substring(index + 9);
		comment = comment.substring(0, comment.length() - 1);
		try {
			comment = new String(comment.getBytes("utf-8"));
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return comment;
	}
}
